﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Text;
using System.Net;
//Khai báo sử dụng thư viện ICSharpCode.SharpZipLib.dll
using ICSharpCode.SharpZipLib.Checksums;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.GZip;
using ICSharpCode.SharpZipLib.Core;

namespace WebServiceSmartphone
{
    public class ApprovalDistributorOrder
    {
        public void WriteFile(string user, string filename, string invdate, string ListOrder, string CurrentServer)
        {
            FileStream fs = null;
            StreamWriter w = null;
            try
            {
                DAL dal = new DAL();
                string sql = "", error = "";
                bool kq = false;
                DataTable ds = new DataTable();

                {
                    sql = "if exists (select name from tempdb.dbo.sysobjects where name = '" + user + "DistributorOrderFullHeader') begin drop table tempdb.dbo.[" + user + "DistributorOrderFullHeader] end";
                    kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref  error);
                    sql = "CREATE TABLE [dbo].[" + user + "DistributorOrderFullHeader](	[InvNo] [varchar](162) NULL,	[SectionIDFixed] [varchar](10) NOT NULL,	[PurchaseOrderNumber] [varchar](162) NULL,	[OrderDate] [varchar](10) NULL,	[DeliveryDate] [varchar](10) NULL,	[MailPromotionNumber] [varchar](100) NOT NULL,	[Shipto] [varchar](100) NULL," +
                           " [CustomerEANNumber] [varchar](100) NULL,	[InternalSupplierCode] [varchar](100) NOT NULL,	[SupplierEANLocationNo] [varchar](100) NOT NULL,	[TermofPaymen] [varchar](100) NOT NULL,	[DiscountPercent] [varchar](100) NOT NULL,	[DiscountAmount] [varchar](100) NOT NULL,	[GrossAmount] [varchar](100) NOT NULL," +
                           " [NetAmount] [varchar](100) NOT NULL,	[TaxAmount] [varchar](100) NOT NULL,	[TotalAmount] [varchar](100) NOT NULL,	[SpecialDiscountAmount] [varchar](100) NOT NULL,	[OrderNote] [varchar](100) NOT NULL,	[PO-TYPE] [varchar](100) NOT NULL,	[CustomerGroup] [varchar](40) NOT NULL,	[OrderMode] [varchar](40) NOT NULL,	[Space] [nvarchar](254) NOT NULL) ON [PRIMARY]";
                    kq = dal.MyExecuteNonQuerys(sql, CurrentServer, "tempdb");
                    sql = "insert into  tempdb.dbo.[" + user + "DistributorOrderFullHeader](InvNo, SectionIDFixed, PurchaseOrderNumber, OrderDate, DeliveryDate, MailPromotionNumber, Shipto, CustomerEANNumber, InternalSupplierCode, SupplierEANLocationNo, TermofPaymen, DiscountPercent, DiscountAmount, GrossAmount, NetAmount, TaxAmount, TotalAmount, SpecialDiscountAmount, OrderNote, [PO-TYPE], CustomerGroup, OrderMode, Space) SELECT DISTINCT InvNo,'H' AS SectionIDFixed, InvNo AS PurchaseOrderNumber, RIGHT(InvDate, 4) + SUBSTRING(InvDate, 4, 2) + LEFT(InvDate, 2) AS OrderDate, ShipDate AS DeliveryDate, '' AS MailPromotionNumber, WHCode AS Shipto, SiteCode AS CustomerEANNumber," +
                          " '' AS InternalSupplierCode, '' AS SupplierEANLocationNo, '' AS TermofPaymen, '' AS DiscountPercent, '' AS DiscountAmount, '' AS GrossAmount, '' AS NetAmount, '' AS TaxAmount, '' AS TotalAmount, " +
                          " '' AS SpecialDiscountAmount, '' AS OrderNote, '' AS [PO-TYPE], 'VNGT' AS CustomerGroup, 'VBAE' AS OrderMode, '' AS Space  FROM  Distributor_Order_Full where Approval=0 and  RIGHT(InvNo, 13) in(" + ListOrder + ") ";
                    kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref  error);

                    sql = "update [" + user + "DistributorOrderFullHeader] set Shipto='' where Shipto=CustomerEANNumber";
                    kq = dal.MyExecuteNonQuerys(sql, CurrentServer, "tempdb");
                    sql = "SELECT     InvNo, SectionIDFixed+PurchaseOrderNumber+OrderDate+ DeliveryDate+ MailPromotionNumber+ Shipto+ CustomerEANNumber+ InternalSupplierCode+ SupplierEANLocationNo+ TermofPaymen+ DiscountPercent+ DiscountAmount+ GrossAmount+ NetAmount+ TaxAmount+ TotalAmount+ SpecialDiscountAmount+ OrderNote+ [PO-TYPE]+ CustomerGroup+ OrderMode+ [Space] as line FROM         tempdb.DBO.[" + user + "DistributorOrderFullHeader]";
                    ds = dal.GetTable(sql, CommandType.Text);

                    if (ds.Rows.Count > 0)
                    {
                        sql = "if exists (select name from tempdb.dbo.sysobjects where name = '" + user + "DistributorOrderFullDetail') begin drop table tempdb.dbo.[" + user + "DistributorOrderFullDetail] end";
                        kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref  error);
                        sql = "CREATE TABLE [dbo].[" + user + "DistributorOrderFullDetail](	[InvNo] [varchar](162) NULL,	[SectionIDFixed] [varchar](100) NOT NULL,	[LineNumber] [varchar](5) NOT NULL,	[SupplierProductCode] [varchar](180) NULL,	[BuyerProductCode] [varchar](180) NULL,	[InternalProductCode] [varchar](180) NULL,	[ItemDescriptioninThai] [varchar](100) NOT NULL,	[OrderedQuantity] [varchar](80) NULL,	[PackagingSize] [varchar](100) NOT NULL,	[UnitType] [varchar](20) NOT NULL,	[UnitPrice] [varchar](13) NULL,	[DiscountPercent] [varchar](50) NOT NULL,	[DiscountAmount] [varchar](120) NOT NULL,	[LineItemAmt] [varchar](120) NULL,	[LineDescription] [nvarchar](500) NOT NULL) ON [PRIMARY]";
                        kq = dal.MyExecuteNonQuerys(sql, CurrentServer, "tempdb");
                        sql = "insert into  tempdb.dbo.[" + user + "DistributorOrderFullDetail]( InvNo, SectionIDFixed, LineNumber, SupplierProductCode, BuyerProductCode, InternalProductCode,ItemDescriptioninThai, OrderedQuantity, PackagingSize, UnitType, UnitPrice, DiscountPercent, DiscountAmount, LineItemAmt, LineDescription)SELECT     InvNo,'D' AS SectionIDFixed, '' AS LineNumber, SKUCode AS SupplierProductCode, SKUCode AS BuyerProductCode, SKUCode AS InternalProductCode, '' AS ItemDescriptioninThai, CONVERT(varchar(8), Quantity) AS OrderedQuantity, '' AS PackagingSize," +
                            " 'CS' AS UnitType,CONVERT(varchar(12),ROUND(CAST (price AS decimal(10,0)),0)) AS UnitPrice, '00.00' AS DiscountPercent, '000000000.00' AS DiscountAmount, CONVERT(varchar(12),ROUND(CAST (price*Quantity AS decimal(10,0)),0))  AS LineItemAmt,  '' AS LineDescription  FROM Distributor_Order_Full where  RIGHT(InvNo, 13) in(" + ListOrder + ") ";
                        kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref  error);

                        sql = "update [" + user + "DistributorOrderFullDetail] set LineItemAmt=CONVERT(varchar(12),ROUND(CAST (UnitPrice AS decimal(10,0))*CAST (OrderedQuantity AS decimal(10,0)),0))";
                        kq = dal.MyExecuteNonQuerys(sql, CurrentServer, "tempdb");

                        sql = "SELECT     SEQ, Length-1 as Length, fieldname, Space FROM  ParameterOrderHeader";
                        DataTable dt = dal.GetTable(sql, CommandType.Text);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            sql = "update [" + user + "DistributorOrderFullHeader] set " + dt.Rows[i]["fieldname"].ToString() + "= left(" + dt.Rows[i]["fieldname"].ToString() + "+'" + dt.Rows[i]["Space"].ToString() + "'," + dt.Rows[i]["Length"].ToString() + ")+' '";
                            kq = dal.MyExecuteNonQuerys(sql, CurrentServer, "tempdb");
                        }

                        sql = "SELECT     SEQ, Length-1 as Length, fieldname, Space FROM   ParameterOrderDetail";
                        dt = dal.GetTable(sql, CommandType.Text);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            sql = "update [" + user + "DistributorOrderFullDetail] set " + dt.Rows[i]["fieldname"].ToString() + "= left(" + dt.Rows[i]["fieldname"].ToString() + "+'" + dt.Rows[i]["Space"].ToString() + "'," + dt.Rows[i]["Length"].ToString() + ")+' '";
                            kq = dal.MyExecuteNonQuerys(sql, CurrentServer, "tempdb");
                        }

                        sql = "SELECT     InvNo, SectionIDFixed+PurchaseOrderNumber+OrderDate+ DeliveryDate+ MailPromotionNumber+ Shipto+ CustomerEANNumber+ InternalSupplierCode+ SupplierEANLocationNo+ TermofPaymen+ DiscountPercent+ DiscountAmount+ GrossAmount+ NetAmount+ TaxAmount+ TotalAmount+ SpecialDiscountAmount+ OrderNote+ [PO-TYPE]+ CustomerGroup+ OrderMode+ [Space] as line FROM         tempdb.DBO.[" + user + "DistributorOrderFullHeader]";
                        ds = dal.GetTable(sql, CommandType.Text);

                        fs = new FileStream(filename, FileMode.Create);
                        w = new StreamWriter(fs, Encoding.ASCII);
                        for (int i = 0; i < ds.Rows.Count; i++)
                        {
                            string line = ds.Rows[i]["line"].ToString();
                            w.WriteLine(line);
                            sql = "SELECT     InvNo, SectionIDFixed, LineNumber, SupplierProductCode+ BuyerProductCode+ InternalProductCode+" +
                                " ItemDescriptioninThai+ OrderedQuantity+ PackagingSize+ UnitType+ UnitPrice+ DiscountPercent+ DiscountAmount+" +
                                " LineItemAmt+ LineDescription as line FROM tempdb.DBO.[" + user + "DistributorOrderFullDetail] WHERE     (InvNo = '" + ds.Rows[i]["InvNo"].ToString() + "') Order by SupplierProductCode";
                            dt = dal.GetTable(sql, CommandType.Text);
                            for (int y = 0; y < dt.Rows.Count; y++)
                            {
                                string LineNumber = ((y + 1).ToString() + "    ").Substring(0, 4);
                                string Dline = dt.Rows[y]["SectionIDFixed"].ToString() + LineNumber + dt.Rows[y]["line"].ToString();
                                w.WriteLine(Dline);
                            }
                        }

                    }
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Flush();
                w.Close();
            }
        }
        public void Zipfile(string outPathname, string password, string fileName)
        {
            string zipFile = outPathname;
            ZipOutputStream zipOut = new ZipOutputStream(File.Create(zipFile));
            zipOut.Password = password;
            FileInfo fileInfo = new FileInfo(fileName);
            ZipEntry entry = new ZipEntry(fileInfo.Name);
            FileStream fileStream = File.OpenRead(fileName);
            byte[] buffer = new byte[Convert.ToInt32(fileStream.Length)];
            fileStream.Read(buffer, 0, (int)fileStream.Length);
            entry.DateTime = fileInfo.LastWriteTime;
            entry.Size = fileStream.Length;
            fileStream.Close();
            zipOut.PutNextEntry(entry);
            zipOut.Write(buffer, 0, buffer.Length);
            zipOut.Finish();
            zipOut.Close();
            //DeleteFile(fileName);
        }
        public void DeleteFile(string path)
        {
            try
            {
                using (StreamWriter sw = File.CreateText(path))
                {

                }
                // Ensure that the target does not exist.
                File.Delete(path);
            }
            catch (Exception e)
            {
                return;
            }
        }
    }
}
